﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;


namespace AsiaSocietyExceltoDatabase
{
    public partial class frmMainMenu : Form
    {

        string fileName;

        public frmMainMenu()
        {
            InitializeComponent();
        }

        private void btnOpenFile_Click(object sender, EventArgs e)
        {
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                fileName = openFileDialog1.FileName;
            }
            txtFileToOpen.Text = fileName;
        }

        private void btnUploadToDB_Click(object sender, EventArgs e)
        {
            AsiaSocietyParser apple = new AsiaSocietyParser(fileName);
            UpLoadToDB(apple.getDataTable);
        }

        private void UpLoadToDB(DataTable dt)
        {
           // MySqlConnection mySQLCon = new MySqlConnection("server=localhost;User Id=root;Pwd=gotiger;database=chinaair;");
            MySqlConnection mySqlCon = new MySqlConnection(Properties.Settings.Default.chinaairConnectionString);
            AsiaSocietyDataSetTableAdapters.tb_city_pollutionTableAdapter myAdapter = new AsiaSocietyDataSetTableAdapters.tb_city_pollutionTableAdapter();

            int rowCount = dt.Rows.Count;

            mySqlCon.Open();
            for (int i = 0; i < dt.Rows.Count; i++)
            {



                DateTime date = Convert.ToDateTime(dt.Rows[i]["Date"]);
                int cityID = Convert.ToInt32(dt.Rows[i]["CityID"]);
                string PM10 = dt.Rows[i]["PM10ID"].ToString();
                string USPM10ID = dt.Rows[i]["USPM10ID"].ToString();
                string API = dt.Rows[i]["API"].ToString();
                string AQI = dt.Rows[i]["AQI"].ToString();
                string PM25ID =  dt.Rows[i]["PM25ID"].ToString();
                string USPM25ID = dt.Rows[i]["USPM25ID"].ToString();

                string sqlComand = "select mid from chinaair.tb_city_pollution where fcityid =" + cityID + " and zdate = '" + date.ToString("yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture) + "'";



                MySqlCommand checkMID = new MySql.Data.MySqlClient.MySqlCommand(sqlComand, mySqlCon);
                MySqlDataReader dr = checkMID.ExecuteReader();

                int MID = 0;

                while (dr.Read())
                {
                    MID = Convert.ToInt32(dr["mid"]);
                }

                dr.Close();


                //string sqlComand = "Insert into chinaair.tb_city_pollution (`zenable`, `zdate`, `fcityid`, `pollutionCN8`, `pollutionUS8`, `pollutionCN7`, `pollutionUS7`, `pollutionCN6`, `pollutionUS6`, `mainimg1`, `comimg1`, `comimg2`, `zvalue`, `defSatellite`, `zeditdate`, `zcreuser`, `zcredate`, `zprevid`, `zlv`) values('N','" +
                //                                                            date + "'," +
                //                                                            cityID + ",'" +
                //                                                            PM10 + "','" +
                //                                                            USPM10ID + "','" +
                //                                                            API + "','" +
                //                                                            AQI + "','" +
                //                                                            PM25ID + "','" +
                //                                                            USPM25ID + "','" +
                //                                                            "','" +
                //                                                            "','" +
                //                                                            "',3,1,'" +
                //                                                            DateTime.Now.ToString("yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture) +"'," +
                //                                                            5 + ",'" +
                //                                                            DateTime.Now.ToString("yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture) + "',0,0)";


                //MySql.Data.MySqlClient.MySqlCommand updateMainImage = new MySql.Data.MySqlClient.MySqlCommand(sqlComand);

                //updateMainImage.Connection = mySQLCon;
                //mySQLCon.Open();
                //updateMainImage.ExecuteNonQuery();
                //mySQLCon.Close();

                if (MID > 0)
                {
                    MySql.Data.MySqlClient.MySqlCommand updateDataSet = new MySql.Data.MySqlClient.MySqlCommand("Update Chinaair.tb_city_pollution Set pollutionCN8 = '" + PM10 + "' , pollutionUS8 = '" + USPM10ID +"', pollutionCN7 = '" + API + "' , pollutionUS7 = '" + AQI + "' , pollutionCN6 ='" + PM25ID + "' , pollutionUS6 = '" + USPM25ID + "' where mid = " + MID);
                    updateDataSet.Connection = mySqlCon;
                    updateDataSet.ExecuteNonQuery();
                }
                else
                {

                    myAdapter.Insert("N",
                                     date,
                                     cityID,
                                     PM10,
                                     USPM10ID,
                                     API,
                                     AQI,
                                     PM25ID,
                                     USPM25ID,
                                     "",
                                     "",
                                     "",
                                     3,
                                     1,
                                     DateTime.Now,
                                     5,
                                     DateTime.Now,
                                     0,
                                     0);
                }
            }

            mySqlCon.Close();
        }



    }
}
